//
//  YiDatabase.swift
//  yiplayer
//
//  Created by wkun on 2024/6/13.
//

import Foundation

public let DB = YiDatabase()
//当前程序的数据库版本号，版本号规则:自然数递增
private let gCurrDataBaseVersion = 1;

public class YiDatabase: NSObject {
    var db: FMDatabase?

    //数据库名
    let dbName = "yiplayer.db"
    //所有的表名
    let tableServer = "table_server"    //添加的服务器
    let tableDBVersion = "table_db_version" //数据库版本号表
    
    public override init() {
        super.init()
        
        self.createDb()
    }
    
    // MARK: - 创建数据库和表
    private func createDb() {
        if let dbPath = self.getDbFilePath() {
            printLog("dbpath:\n\(dbPath)")
            //数据库不存在，则创建表
            db = FMDatabase.init(path: dbPath)
            
            if FileManager.default.fileExists(atPath: dbPath) == false {
                let ret = self.createTableV1()
                if ret == false {
                    //创建失败，删除数据库文件
                    do {
                        try FileManager.default.removeItem(atPath: dbPath)
                    } catch  {
                    }
                }
            }
            
            ///若有必要的话，升级数据库
            DispatchQueue.main.async {
                self.updateDatabase()
            }
        }
    }
    
    /// 创建V1表
    private func createTableV1() -> Bool{
        if let db = self.db {
            if db.open() == false {
                print("创建V1数据库的表时，打开数据库失败")
                return false
            }
            
            //服务名、服务地址、路径(可选)、用户名、密码、服务类型（smb\ftp\dav）、图标名称、备注
            //name text, ip text, path text, username text,pwd text, type Integer, imgname text, note text
            let sqlServer = "name text, ip text, path text, username text, pwd text, type Integer, imgname text, note text".sql(tableServer)
            
            //版本表 建表时，插入建表时的db版本
            let sqlDBVersion = "dbVersion Integer".sql(tableDBVersion)
            
            let sql = "\(sqlServer);\(sqlDBVersion)"
            let ret = db.executeStatements(sql)
            
            db.close()
            if ret == false {
                print("创建V1表失败")
                return false
            }
            return true
        }
        return false
    }
    
    
    
    
    private func updateDatabase() {
        
    }
    
    // MARK: - 封装代码
    private func openDb() {
        if (self.db?.isOpen ?? false) == false {
            self.db?.open()
        }
    }
    
    func executeUpdate(sql: String?) -> Bool {
        if sql == nil {
            return false
        }
        
        self.openDb()
        
        do {
            try self.db?.executeUpdate(sql!, values: nil)
            return true
        } catch  {
            print("插入数据错误")
        }
        return false
    }
}

extension YiDatabase {
    func getDbFilePath() -> String? {
        let rootpath = BLPath.cacheDataRootPath()
        if let p = BLPath.createPath(rootpath, dirName: "database") {
            let dbName = "yiplayer.db"
            return p+"/"+dbName
        }
        return nil;
    }
}

///根据字段的名字与类型，来生成sql
private extension String {
    func sql(_ tableName: String) -> String{
        let fields = self
        //创建表时，添加时间戳字段timestamp 用来排序
    //    NSString *ct = @"sortNum Integer AUTOINCREMENT, createdTime TimeStamp NOT NULL DEFAULT (DATETIME('now','localtime'))";
        let ct = "createTime TimeStamp NOT NULL DEFAULT (DATETIME('now','localtime')) , timestamp TimeStamp NOT NULL DEFAULT (strftime('%s%f','now'))";
        
//        let dd = "create table if not exists \(tableName) (ID Integer PRIMARY KEY AUTOINCREMENT, \(fields), \(ct)"
        
        //其他的附加字段（备用）
        let otherfields = "field1 text, field2, text, field3 text"
        
        let dd = "create table if not exists \(tableName) (ID Integer PRIMARY KEY AUTOINCREMENT, \(fields), \(otherfields), \(ct))"
        return dd
    }
}

